﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IHISCETeim.Library;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IHISCETeim.Model;

namespace IHISCETeim.DAL
{
    public  class FTradeBalanceDAL
    {
        /// <summary>
        /// 添加新的冲帐记录
        /// 1,需处理相应的应收记录金额及状态
        /// 2.需处理收款列表余额及状态
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static bool Add(List<FTradeBalanceInfo> objs, decimal ShengYuJinE)
        {
             Database DB = DatabaseFactory.CreateDatabase();
             using (DbConnection conn = DB.CreateConnection())
             {
                 //打开连接
                 conn.Open();
                 //创建事务
                 DbTransaction trans = conn.BeginTransaction();
                 try
                 {
                     foreach (FTradeBalanceInfo obj in objs)
                     {
                         DbCommand dbCommand = DB.GetStoredProcCommand("FTradeBalance_Add", obj.FCatheringId, obj.FTradeAgeId, obj.BalanceMoney, obj.RealName, obj.Remark, ShengYuJinE,obj.YiChongJinE,obj.CreateDate,obj.FnncLsId);
                         int FbalanceId=Convert.ToInt32(  DB.ExecuteScalar(dbCommand, trans));
                         if (FbalanceId == -1)
                         {
                             throw new Exception(string.Format("金额{0}不能进行冲帐,可能已有人冲过，请确认后再操作.",obj.BalanceMoney));
                         }
                         ShengYuJinE = ShengYuJinE - obj.BalanceMoney;
                     }
                     trans.Commit();
                     return true;
                 }
                 catch
                 {
                     //发生异常，事务回滚
                     trans.Rollback();
                     return false;
                     //return exc.Message;
                 }
                 finally
                 {
                     //关闭连接
                     conn.Close();
                 }
             }

            
        }
        /// <summary>
        /// 调帐操作
        /// </summary>
        /// <param name="obj">添加新的　收款记录</param>
        /// <param name="FbalanceIds">原冲帐记录列表</param>
        /// <returns></returns>
        public static bool AddAdjusting(FTradeCatheringInfo obj, List<int> FbalanceIds)
        {
             int NewFCatheringId;
             Database DB = DatabaseFactory.CreateDatabase();
             using (DbConnection conn = DB.CreateConnection())
             {
                 //打开连接
                 conn.Open();
                 //创建事务
                 DbTransaction trans = conn.BeginTransaction();
                 try
                 {
                     //1.
                     DbCommand dbCommandCather = DB.GetStoredProcCommand("FTradeCathering_Add", obj.FCTitle, obj.ShoukuanRiQi, obj.JingXiaoShang, obj.JinE, obj.KuanXiangLeiXing, obj.RealName, obj.IsAdjustable, obj.State, obj.Remark, "系统", obj.MoneyFrom, obj.oldKuanXiangLeiXing);
                     NewFCatheringId= Convert.ToInt32(DB.ExecuteScalar(dbCommandCather,trans));

                     foreach (int FbalanceId in FbalanceIds)
                     {
                         //2
                         DbCommand dbCommand = DB.GetStoredProcCommand("FTradeBalance_AddAdjusting", FbalanceId, NewFCatheringId);
                         DB.ExecuteScalar(dbCommand, trans);
                     }
                    trans.Commit();
                    return true;
                 }
                 catch
                 {
                     //发生异常，事务回滚
                     trans.Rollback();
                     return false;
                     //return exc.Message;
                 }
                 finally
                 {
                     //关闭连接
                     conn.Close();
                 }
             }
        }

        /// <summary>
        /// 调帐操作2,用于 做调帐 时用
        /// </summary>
        /// <param name="obj">添加新的　收款记录</param>
        /// <param name="FbalanceIds">原冲帐记录列表</param>
        /// <param name="FbalanceIds">调帐金额列表</param>
        /// <returns></returns>
        public static bool AddAdjusting2(FTradeCatheringInfo obj, List<int> FbalanceIds, List<decimal> AdjustingMoneys, List<decimal> oldAdjustingMoneys)
        {

            int NewFCatheringId;
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    //1.
                    DbCommand dbCommandCather = DB.GetStoredProcCommand("FTradeCathering_Add", obj.FCTitle, obj.ShoukuanRiQi, obj.JingXiaoShang, obj.JinE, obj.KuanXiangLeiXing, obj.RealName, obj.IsAdjustable, obj.State, obj.Remark, "系统", obj.MoneyFrom, obj.oldKuanXiangLeiXing);
                    NewFCatheringId = Convert.ToInt32(DB.ExecuteScalar(dbCommandCather, trans));

                    obj.FCatheringId = NewFCatheringId;
                    //foreach (int FbalanceId in FbalanceIds)
                    //{
                        
                    //    //2
                    //    DbCommand dbCommand = DB.GetStoredProcCommand("FTradeBalance_AddAdjusting", FbalanceId, NewFCatheringId);
                    //    DB.ExecuteScalar(dbCommand, trans);
                    //}


                    for(int index=0;index<FbalanceIds.Count;index++)
                    {
                        DbCommand dbCommand = DB.GetStoredProcCommand("FTradeBalance_AddAdjusting2", FbalanceIds[index], NewFCatheringId, AdjustingMoneys[index], oldAdjustingMoneys[index]);
                        int result=Convert.ToInt32( DB.ExecuteScalar(dbCommand, trans));
                        if (result == -1)
                        {
                            throw new Exception("唯一性不对.");
                        }
                    }

                    trans.Commit();
                    return true;
                }
                catch
                {
                    //发生异常，事务回滚
                    trans.Rollback();
                    return false;
                    //return exc.Message;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
        }


        public static bool AddAdjusting2(Dictionary<int, FTradeCatheringInfo> FTradeCatheringList, Dictionary<int, List<FTradeAdjustingTemp>> FTradeAdList)
        {
            int NewFCatheringId;
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    foreach (int oldFCID in FTradeCatheringList.Keys)
                    {
                        FTradeCatheringInfo obj = FTradeCatheringList[oldFCID];
                        //1.
                        DbCommand dbCommandCather = DB.GetStoredProcCommand("FTradeCathering_Add", obj.FCTitle, obj.ShoukuanRiQi, obj.JingXiaoShang, obj.JinE, obj.KuanXiangLeiXing, obj.RealName, obj.IsAdjustable, obj.State, obj.Remark, "系统", obj.MoneyFrom, obj.oldKuanXiangLeiXing);
                        NewFCatheringId = Convert.ToInt32(DB.ExecuteScalar(dbCommandCather, trans));
                        obj.FCatheringId = NewFCatheringId;

                        //2.
                        foreach (FTradeAdjustingTemp itmObj in FTradeAdList[oldFCID])
                        { 
                        //for (int index = 0; index < FbalanceIds.Count; index++)
                        //{
                            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeBalance_AddAdjusting2", itmObj.FBalanceId, NewFCatheringId, itmObj.AdjustingMoney, itmObj.OldAdjustingMoney);
                            int result = Convert.ToInt32(DB.ExecuteScalar(dbCommand, trans));
                            if (result == -1)
                            {
                                throw new Exception("唯一性不对.");
                            }
                        //}
                        }
                    }
                    trans.Commit();
                    return true;
                }
                catch
                {
                    //发生异常，事务回滚
                    trans.Rollback();
                    return false;
                    //return exc.Message;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 获取冲帐记录或可调帐的记录数
        /// </summary>
        /// <param name="WhereSql"></param>
        /// <returns></returns>
        public static DataTable GetAddAdjustingList(string WhereSql)
        {
            //Database DB = DatabaseFactory.CreateDatabase();

            //DbCommand dbCommand = DB.GetStoredProcCommand("FTradeBalance_GetAddAdjustingList", WhereSql);
            //return DB.ExecuteDataSet(dbCommand).Tables[0];

            int TotalCount = 0;

            return  GetAddAdjustingList(WhereSql, 1, 999999, "CreateDate DESC", ref TotalCount);

         

        }
        /// <summary>
        /// 获取冲帐记录或可调帐的记录
        /// </summary>
        /// <param name="WhereSql"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="Sort"></param>
        /// <param name="TotalCount"></param>
        /// <returns></returns>
        public static DataTable GetAddAdjustingList(string WhereSql, int PageIndex, int PageSize, string Sort, ref int TotalCount)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeBalance_GetAddAdjustingList");
            DB.AddInParameter(dbCommand, "WhereSql", DbType.String, WhereSql);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String, Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32, 8);
            DataTable dt = DB.ExecuteDataSet(dbCommand).Tables[0];

            TotalCount = Convert.ToInt32(DB.GetParameterValue(dbCommand, "TotalCount"));
            return dt;
        }
        /// <summary>
        /// 根据充帐ID，获取其下所有的冲帐记录
        /// </summary>
        /// <param name="FCatheringId"></param>
        /// <returns></returns>
        public static DataTable GetFTradeBalanceByFCatheringId(int FCatheringId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeBalance_GetByFCatheringId", FCatheringId);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 根据机号，获取其下所有的冲帐记录
        /// </summary>
        /// <param name="FCatheringId"></param>
        /// <returns></returns>
        public static DataTable GetFTradeBalanceByJiHao(string JiHao)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FTradeBalance_GetByJiHao", JiHao);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
      
    }
}
